Joins

Add horizontal or vertical joins to the data flow in order to join tables in the database.

Video

Unions in Model

Note: relationship 'joins' between tables can defined during the Data Model phase. These joins will be added at runtime during the query, rather than being stored in the database.

Merge Join (horizontal)

Add a join by dragging the Join tile onto the canvas, and dragging a connection from two tables to the Join tile. Name the resulting table. In the Join Node window, the tables will be listed under Join Columns. Select the required join type, and then click Add Join Columns and add columns.

In the Join Columns window, the column on the left is the left join, and on the right is the right join.

In the screenshot below, an inner join is used to join the customerProfile and customers tables, based on the profileKey column which appears in both tables.

 

Union or Stack Join (vertical)

To add a union, drag the Union tile onto the canvas and connect the two relevant nodes to it. Then name the resulting table, and select the relevant columns.

By default, the Union All option is selected. This appends the rows in the second table to the rows in the first table, including duplicate rows. To eliminate duplicate rows, uncheck the Union All option.

To add a column with the name of the source table, check Add Source Table Name Column.

Previewing Joins

By default, the preview panel displays 50 rows, which may not be enough to preview joins. Use the Preview Size drop down in ribbon to show more rows in the preview panel.